package com.augurit.gzcgwsp.csshlj.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.jbpm.api.ProcessEngine;
import org.jbpm.api.task.Task;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.PropertyFilter;
import com.augurit.common.system.web.auth.form.LoginUserForm;
import com.augurit.common.util.HqlUtils;
import com.augurit.common.util.PageUtils;
import com.augurit.common.util.ReflectionUtils;
import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.csshlj.convert.YwHwCzConvertor;
import com.augurit.gzcgwsp.csshlj.dao.CsshljCzDao;
import com.augurit.gzcgwsp.csshlj.dao.PersonDao;
import com.augurit.gzcgwsp.csshlj.entity.Csshljcz;
import com.augurit.gzcgwsp.csshlj.entity.Person;
import com.augurit.gzcgwsp.csshlj.service.ICcljclcService;
import com.augurit.gzcgwsp.csshlj.service.ICsshljCzService;
import com.augurit.gzcgwsp.csshlj.service.ICsshljclcService;
import com.augurit.gzcgwsp.csshlj.service.IShljYsService;
import com.augurit.gzcgwsp.csshlj.web.form.CcljclcForm;
import com.augurit.gzcgwsp.csshlj.web.form.CsshljCzForm;
import com.augurit.gzcgwsp.csshlj.web.form.CsshljclcForm;
import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.excelimportexport.util.FileUtil;
import com.augurit.gzcgwsp.excelimportexport.util.GetFileName;
import com.augurit.gzcgwsp.sendNotice.service.IExchangeStateService;
import com.augurit.gzcgwsp.sendNotice.service.IYwMtbjlshInfoService;
import com.augurit.gzcgwsp.sendNotice.service.IYwWebserviceForWbService;
import com.augurit.gzcgwsp.sendNotice.service.IYwWebserviceInfoService;
import com.augurit.gzcgwsp.smartformCsdlwj.common.SmartDao;
import com.augurit.gzcgwsp.smartformCsdlwj.service.ISmartFormShljqsService;
import com.augurit.gzcgwsp.wfex.common.service.IWfServiceEX;
import com.augurit.gzcgwsp.ywcmn.entity.YwComQyxx;
import com.augurit.gzcgwsp.ywcmn.entity.YwComSjcllist;
import com.augurit.gzcgwsp.ywcmn.service.IYwComQyxxService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjclService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjcllistService;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComQyxxForm;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjclForm;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjcllistForm;
import com.augurit.smartform.service.IWfSupportService;
import com.augurit.wf.bi.convert.WfBusInstanceConvertor;
import com.augurit.wf.bi.service.IWfBusInstanceService;
import com.augurit.wf.bi.web.form.WfBusInstanceForm;
import com.augurit.wf.bi.web.form.WfBusInstanceTaskForm;
import com.augurit.wf.common.constant.WfConstant;
import com.augurit.wf.common.entity.WfBusInstance;
import com.augurit.wf.common.service.IWfService;


@Service
@Transactional
public class CsshljCzServiceImpl implements ICsshljCzService{
	private static final Logger log=Logger.getLogger(CsshljCzServiceImpl.class);
    @Autowired
	private IWfSupportService wfSupportService;
    @Autowired
	private CsshljCzDao csshljCzDao;
	@Autowired
	private SmartDao smartDao;
    @Autowired
    private IYwComSjclService ywComSjclService;
	@Autowired
	private IWfBusInstanceService wfBusInstanceService;
	@Autowired
	private PersonDao personDao;
	@Autowired
	private IYwComSjcllistService ywComSjcllistService;	   
	@Autowired
	private IYwComQyxxService ywComQyxxService;  
	@Autowired
    private IWfService wfService;
	@Autowired
    private IWfServiceEX wfServiceEX;
	@Autowired
    private ProcessEngine processEngine;
	@Autowired
    private ISmartFormShljqsService smartFormShljqsService;
	@Autowired
    private IShljYsService shljYsService;
	@Autowired
    private ICsshljclcService csshljclcService;
	@Autowired
    private ICcljclcService ccljclcService;
	@Autowired
    private IYwMtbjlshInfoService ywMtbjlshInfoService;
    @Autowired
	private IYwWebserviceInfoService ywWebserviceInfoService;
    @Autowired
    private IYwWebserviceForWbService ywWebserviceForWbService;
    @Autowired
    private IExchangeStateService exchangeStateService;
	
    /*
     * 保存(non-Javadoc)
     * @see com.augurit.gzcgwsp.smartformCsdlwj.service.IYwCsdlwjService#wfBusSave(javax.servlet.http.HttpServletRequest, java.lang.String, java.lang.String)
     */
	public WfBusInstanceTaskForm wfBusSave(HttpServletRequest request,CsshljCzForm form,Map map,String username) {
		WfBusInstanceTaskForm task = wfSupportService
		.wfBusSave(request);
			if(task==null)
				return null;
			String entityKey = task.getMasterEntityKey();
			String ysl = (String) map.get("shlj_cz_ysl");
			String sqdw = (String) map.get("shlj_cz_qymc"); // 申请单位
			String acceptNo = updateAcceptNoAndSumary(entityKey, sqdw, ysl,username); // 更新入受理号以及汇总字段
			Long id = null;
			if (task != null && task.getMasterEntityKey() != null) {
				id = Long.valueOf(task.getMasterEntityKey());
			}
			form.setId(id);
			task.setMessage(acceptNo); // 设置受理编号
			if (ysl.equals("1")) { // 更新预受理状态
				this.changeYslState(id, 2l,null);
			}
			form.setId(id);
			String qyid=(String) map.get("shlj_cz_qybh");//企业id
			String frdb=(String) map.get("shlj_cz_frdb");//法人代表
			String yyzzzch=(String) map.get("shlj_cz_yyzzzch");//营业注册号码
			String tel=(String) map.get("shlj_cz_lxdh");//联系电话
			String regmoney=(String) map.get("shlj_cz_zczb");//注册资金
			String fax=(String) map.get("shlj_cz_cz");//传真
			String email=(String) map.get("shlj_cz_email");//电子邮箱
			String post=(String) map.get("shlj_cz_yzbm");//邮编
			String address=(String) map.get("shlj_cz_bgzs");//办公住所
			String zsbh=(String) map.get("shlj_cz_fzh");//证书编号
			map.put("qyid", qyid);
			map.put("tel",tel);
			map.put("sqdw",sqdw);
			map.put("frdb",frdb);
			map.put("yyzzzch", yyzzzch);
			map.put("regmoney", regmoney);
			map.put("fax", fax);
			map.put("email", email);
			map.put("post", post);
			map.put("address", address);
			if(zsbh!=null && zsbh.length()>0){
				map.put("zsbh",zsbh);
			}
			Long qybh=saveEnterpriseInfo("shljcz", map);
			if(qybh!=null){
				form.setQybh(String.valueOf(qybh));
				this.updateEntityByQyId(qybh,id);//设置企业编号
			}
			return task;
	}
	
	/**
	 * 设置企业id
	 * @param qybh
	 * @param id
	 */
	private void updateEntityByQyId(Long qybh, Long id) {
		String sql=" update SHLJ_CZ_V1 set QYBH="+qybh +" where id="+id ;
		this.csshljCzDao.updateDateBase(sql);
	}

	/**
	 * 改变预受理状态
	 * @param id
	 * @param state
	 */
    public void changeYslState(Long id,Long state,String yslThyy) {

    	StringBuffer sql = new StringBuffer();
    	sql.append("update Csshljcz t set t.ysl='"+state+"' ");
    	if(StringUtils.isNotBlank(yslThyy)){
    		sql.append(" ,t.yslThyy='"+yslThyy+"'");
    	}
    	sql.append(" where t.id="+id);
    	csshljCzDao.getSession().createQuery(sql.toString()).executeUpdate();
    	System.out.println("水域收件");
    }
    
	/**
	 * 更新受理号 汇总字段
	 * 
	 * @param entityId
	 * @return
	 * @throws ParseException
	 */
	private String updateAcceptNoAndSumary(String entityId, String sqdw,
			String ysl,String username) {
		String acceptNo = "";

		//判断受理号是否存在，更新受理号和汇总字段
		if (entityId != null && entityId.trim().length() > 0) {
			String isExist = "select ACCEPT_NO from SHLJ_CZ_V1 where id = "
					+ entityId;
			List list = smartDao.getResultList(isExist);
			if (list != null) {
				Object obj = list.get(0);
				String s = (String) obj;
				if (s == null || s.trim().length() <= 0) {

					// ===================生成受理编号，更新受理编号开始========
//					OaDocCodeForm doccodeForm = docCodeService.get(10L);
//					acceptNo = CommonUtils.convertSeqToAcceptNo(doccodeForm
//							.getSeq());
//					docCodeService.updateForAddSeq(doccodeForm.getId());
					//生成政务中心统一查询编号 开始
					Map<String,String> serialNoAndPasswordMap = null;
		        	serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("0");//大厅
		        	String serialNo = serialNoAndPasswordMap.get("serialNo");
		        	String password = serialNoAndPasswordMap.get("password");
		        	//生成政务中心统一查询编号 结束
		        	acceptNo = serialNo;
					String sql = "Update SHLJ_CZ_V1 SET ACCEPT_NO ='"
							+ serialNo
							+ "',SERIAL_NO='"
							+ serialNo
							+"',PASSWORD='"
							+ password
							+ "' , SJR = '"
							+ username
							+ "' where ID = " + entityId;
					smartDao.executeSql(sql);
					// ==================生成受理编号，更新受理编号结束==========

					// 更新汇总
					WfBusInstance entity = wfBusInstanceService
							.getByMasterEntityAndKey("Csshljcz", entityId);
					entity.setBusMemo1(sqdw);
					entity.setBusMemo3(serialNo);
					entity.setBusMemo4("城市生活垃圾经营性处置服务许可审批");
					entity.setBusDate2(new Date());
					WfBusInstanceForm form = WfBusInstanceConvertor
							.convertVoToForm(entity);
					wfBusInstanceService.save(form);

				} else if ((s != null && s.trim().length() > 0) && ysl.equals("1")) { // 预受理状态下，更新汇总字段
					//设置收件人  开始
					String sql = "Update SHLJ_CZ_V1 SET SJR = '"
						+ username
						+ "' where ID = " + entityId;
					smartDao.executeSql(sql);
					//设置收件人  结束
					acceptNo = s;
					WfBusInstance entity = wfBusInstanceService
							.getByMasterEntityAndKey("Csshljcz", entityId);
					entity.setBusMemo1(sqdw);
					entity.setBusMemo3(acceptNo);
					entity.setBusMemo4("城市生活垃圾经营性处置服务许可审批");
					entity.setBusDate2(new Date());
					WfBusInstanceForm form = WfBusInstanceConvertor
							.convertVoToForm(entity);
					wfBusInstanceService.save(form);

				} else {
					acceptNo = s;
				}
			}
		}
		return acceptNo;
	}
	
	public List<Csshljcz> getByAcceptNo(String acceptNo)
	{
		String sql = "from Csshljcz t where t.acceptNo='" + acceptNo + "'";
        List<Csshljcz> entityList = csshljCzDao.find(sql);
        return entityList;
	}
	
	
	/*
	 * 主要用于证书打印(non-Javadoc)
	 * @see com.augurit.gzcgwsp.csshlj.service.ICsshljCzService#getForm(java.lang.String, java.lang.String)
	 */
	public CsshljCzForm getForm(Long id, String tablename) {
		String sql=" select *　from "+tablename+" where id="+id;
		List list=csshljCzDao.queryForList(sql);
		CsshljCzForm form=new CsshljCzForm();
		  if(list!=null && list.size()>0){
   		   Map<String,String> map=(Map<String,String>) list.get(0);
             form.setId(Long.valueOf((String)map.get("id")));
             form.setAcceptNo(map.get("accept_no"));
             form.setSqdw(map.get("qymc"));
             form.setZsbh(map.get("fzh"));
             form.setQylx(map.get("qylx"));
             form.setYyzch(map.get("yyzzzch"));
             form.setFddbr(map.get("frdb"));
             //补交告知  //补交受理
             if(StringUtils.isNotBlank(map.get("bjgzsj"))){
            	 try {
					Date bjgzsjdate=DateUtilsEX.dateFormate(map.get("bjgzsj"), "yyyy年MM月dd日");
					 form.setBjgzsj(bjgzsjdate);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
             }
             if(StringUtils.isNotBlank(map.get("bjslsj"))){
            	 try {
					Date bjslsjdate=DateUtilsEX.dateFormate(map.get("bjslsj"), "yyyy年MM月dd日");
					 form.setBjslsj(bjslsjdate);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
             }
             
             form.setBjgzfcr(map.get("bjgzfcr"));
             form.setBjgzyy(map.get("bjgzyy"));
             form.setBjgzqd(map.get("bjgzqd"));
             form.setBjgzflag(map.get("bjgzflag"));
             
             form.setSldwcbrxm(map.get("sldwcbrxm"));
             form.setBjsljtdd(map.get("bjsljtdd"));
             form.setBjclqd(map.get("bjclqd"));
             //告知结束
             
             form.setProjectName(map.get("xmmc"));//项目名称
             form.setXkContent((map.get("xkcontent")));
             String startDate=map.get("jysjsstr");//经营开始时间
             String endDate=map.get("jysjestr");//经营结束时间
             String fzsj=map.get("fzsjstr");//发证时间
             if(StringUtils.isBlank(map.get("xkcontent"))){
            	 form.setXkContent("城市生活垃圾经营性处置 ");
             }
             if(StringUtils.isNotBlank(startDate)){
            	if(startDate.length()>=10){
	     			form.setStartYear(startDate.substring(0,4));
	     			form.setStartMonth(startDate.substring(5,7));
	     			form.setStartDay(startDate.substring(8,10));
            	}
     		 }else{
     			form.setStartYear(DateUtilsEX.getCurYear());
     			form.setStartMonth(DateUtilsEX.getCurMonth());
     			form.setStartDay(DateUtilsEX.getCurDay());
     		 }
             if(StringUtils.isNotBlank(endDate)){
            	 if(endDate.length()>=10){
            	   form.setEndYear(endDate.substring(0,4));
        			form.setEndMonth(endDate.substring(5,7));
        			form.setEndDay(endDate.substring(8,10));
            	 }
      		}else{
      			form.setEndYear(DateUtilsEX.getCurYear());
      			form.setEndMonth(DateUtilsEX.getCurMonth());
      			form.setEndDay(DateUtilsEX.getCurDay());
      		}
            if(StringUtils.isNotBlank(fzsj)){
            	 if(fzsj.length()>=10){
	       			form.setFzYear(fzsj.substring(0,4));
	       			form.setFzMonth(fzsj.substring(5,7));
	       			form.setFzDay(fzsj.substring(8,10));
            	 }
       		}else{
    			form.setFzYear(DateUtilsEX.getCurMonth());
      			form.setFzMonth(DateUtilsEX.getCurMonth());
      			form.setFzDay(DateUtilsEX.getCurDay());
       		}
             form.setJdTel(map.get("jdtel"));
             form.setAddress(map.get("address"));
             form.setQyManagerZw((map.get("qymanagerzw")));
             form.setQyManager(map.get("qymanager"));
             form.setQyManagerZc(map.get("qymanagerzc"));
//             form.setQylx(map.get("qyxx"));
             form.setQygyzb(map.get("qygyzb"));
             form.setQygrzb(map.get("qygrzb"));
             form.setQyhzzb(map.get("qyhzzb"));
             form.setQywszb(map.get("qywszb"));
             form.setFddbrZc(map.get("fddbrzc"));
             form.setFddbrZw(map.get("fddbrzw"));
             form.setAddress(map.get("address"));
             form.setZczb(map.get("zczb"));
             form.setFzrq(fzsj==null?"":fzsj);
             form.setFzjg(map.get("fzjg")==null?"广州市城市管理委员会":map.get("fzjg"));
             form.setCbyw(map.get("cbyw"));
             form.setSjr(map.get("sjr"));
             
 			//设置收件通知书书签
             form.setQymc(form.getSqdw());
             form.setFrdb(form.getFddbr());
             form.setCbyw2(form.getCbyw());
             form.setCbyw3(form.getCbyw());
             form.setCbyw4(form.getCbyw());
             form.setFrdb2(form.getFddbr());
             form.setAcceptNo2(map.get("accept_no"));
//             String tbrqStr = simpleDateFormat.format(form.getTbrq());
             form.setTbrq2(map.get("sqrq"));
             form.setTbrq3(map.get("sqrq"));
             form.setCurDate2(map.get("sqrq"));
             form.setCurDate3(map.get("sqrq"));
             form.setSjr2(form.getSjr());
             form.setSajg(form.getFzjg());
             form.setSajg2(form.getFzjg());
             form.setQymc2(form.getSqdw());
          }
		return form;
	}
	
	/**
	 * 获取收件通知书里面的材料列表
	 * @param form
	 * @return
	 */
	public CsshljCzForm getSjtzsField(CsshljCzForm form){
		form.setSerialNo2(form.getSerialNo());
		form.setPassword2(form.getPassword());
		Long id = form.getId();
		YwComSjcllistForm sjclListForm=new YwComSjcllistForm();
		sjclListForm.setAjid(id);
		sjclListForm.setTplcode("shljcz");
        List<YwComSjcllistForm> sjclList = ywComSjcllistService.getList(sjclListForm);
        YwComSjclForm fkForm = new YwComSjclForm();
//        fkForm.setFkid(form.getQyid());
        fkForm.setAjid(id);
        fkForm.setTplcode("shljcz");
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        for (YwComSjcllistForm listForm : sjclList) {
            boolean hasExists = false;
            for (YwComSjclForm tplForm : sjclTpl) {
                if (tplForm.getTplid() != null && listForm.getId().longValue() == tplForm.getTplid().longValue()) {
                    if (StringUtils.isNotBlank(tplForm.getFilepath())) {
                        listForm.setState("1");
                        listForm.setHasfile("1");
                        listForm.setFilename(tplForm.getFilename());
                        listForm.setFilepath(tplForm.getFilepath());
                        listForm.setFiletype(tplForm.getFiletype());
                    } else {
                        listForm.setState("0");
                        listForm.setHasfile("0");
                    }
                    if (tplForm.getYjs() != null) {
                        listForm.setYjs(Long.valueOf(tplForm.getYjs()));
                    }
                    if (tplForm.getFyjs() != null) {
                        listForm.setFyjs(Long.valueOf(tplForm.getFyjs()));
                    }
                    listForm.setClid(tplForm.getId());
                    if ("1".equals(listForm.getSfdz())) {
                        listForm.setDisabled("1");
                    } else {
                        listForm.setDisabled("0");
                        if (tplForm.getSfdz() == null) {
                            listForm.setSfdz("0");
                        } else {
                            listForm.setSfdz(tplForm.getSfdz());
                        }
                    }
                    hasExists = true;
                }
            }
            if (!hasExists) {
                listForm.setState("0");
                listForm.setHasfile("0");
                if ("1".equals(listForm.getSfdz())) {
                    listForm.setDisabled("1");
                } else {
                    listForm.setDisabled("0");
                }
            }
        }
        if(sjclList.size()>0)
		{
				YwComSjcllistForm sjclForm01=sjclList.get(0);
				form.setTitleRow101(sjclForm01.getTitle());
				form.setTitleRow102(sjclForm01.getTitle());
				form.setFyjsRow101(sjclForm01.getFyjs());
				form.setFyjsRow102(sjclForm01.getFyjs());
				form.setFsRow101(sjclForm01.getYjs()+sjclForm01.getFyjs());
				form.setFsRow102(sjclForm01.getYjs()+sjclForm01.getFyjs());
				if("0".equals(sjclForm01.getSfdz()))
				{
					form.setSfdzRow101("√");
					form.setSfdzRow102("√");
				}else{
					form.setSfdzRow101("√");
					form.setSfdzRow102("√");
				}
				
				YwComSjcllistForm sjclForm02=sjclList.get(1);
				form.setTitleRow201(sjclForm02.getTitle());
				form.setTitleRow202(sjclForm02.getTitle());
				form.setFyjsRow201(sjclForm02.getFyjs());
				form.setFyjsRow202(sjclForm02.getFyjs());
				form.setFsRow201(sjclForm02.getYjs()+sjclForm02.getFyjs());
				form.setFsRow202(sjclForm02.getYjs()+sjclForm02.getFyjs());
				if("0".equals(sjclForm02.getSfdz()))
				{
					form.setSfdzRow201("√");
					form.setSfdzRow202("√");
				}else{
					form.setSfdzRow201("√");
					form.setSfdzRow202("√");
				}
				
				
				YwComSjcllistForm sjclForm03=sjclList.get(2);
				form.setTitleRow301(sjclForm03.getTitle());
				form.setTitleRow302(sjclForm03.getTitle());
				form.setFyjsRow301(sjclForm03.getFyjs());
				form.setFyjsRow302(sjclForm03.getFyjs());
				form.setFsRow301(sjclForm03.getYjs()+sjclForm03.getFyjs());
				form.setFsRow302(sjclForm03.getYjs()+sjclForm03.getFyjs());
				if("0".equals(sjclForm03.getSfdz()))
				{
					form.setSfdzRow301("√");
					form.setSfdzRow302("√");
				}else{
					form.setSfdzRow301("√");
					form.setSfdzRow302("√");
				}
				
				YwComSjcllistForm sjclForm04=sjclList.get(3);
				form.setTitleRow401(sjclForm04.getTitle());
				form.setTitleRow402(sjclForm04.getTitle());
				form.setFyjsRow401(sjclForm04.getFyjs());
				form.setFyjsRow402(sjclForm04.getFyjs());
				form.setFsRow401(sjclForm04.getYjs()+sjclForm04.getFyjs());
				form.setFsRow402(sjclForm04.getYjs()+sjclForm04.getFyjs());
				if("0".equals(sjclForm04.getSfdz()))
				{
					form.setSfdzRow401("√");
					form.setSfdzRow402("√");
				}else{
					form.setSfdzRow401("√");
					form.setSfdzRow402("√");
				}
				
				
				YwComSjcllistForm sjclForm05=sjclList.get(4);
				form.setTitleRow501(sjclForm05.getTitle());
				form.setTitleRow502(sjclForm05.getTitle());
				form.setFyjsRow501(sjclForm05.getFyjs());
				form.setFyjsRow502(sjclForm05.getFyjs());
				form.setFsRow501(sjclForm05.getYjs()+sjclForm05.getFyjs());
				form.setFsRow502(sjclForm05.getYjs()+sjclForm05.getFyjs());
				if("0".equals(sjclForm05.getSfdz()))
				{
					form.setSfdzRow501("√");
					form.setSfdzRow502("√");
				}else{
					form.setSfdzRow501("√");
					form.setSfdzRow502("√");
				}
				
				YwComSjcllistForm sjclForm06=sjclList.get(5);
				form.setTitleRow601(sjclForm06.getTitle());
				form.setTitleRow602(sjclForm06.getTitle());
				form.setFyjsRow601(sjclForm06.getFyjs());
				form.setFyjsRow602(sjclForm06.getFyjs());
				form.setFsRow601(sjclForm06.getYjs()+sjclForm06.getFyjs());
				form.setFsRow602(sjclForm06.getYjs()+sjclForm06.getFyjs());
				if("0".equals(sjclForm06.getSfdz()))
				{
					form.setSfdzRow601("√");
					form.setSfdzRow602("√");
				}else{
					form.setSfdzRow601("√");
					form.setSfdzRow602("√");
				}
				
				YwComSjcllistForm sjclForm07=sjclList.get(6);
				form.setTitleRow701(sjclForm07.getTitle());
				form.setTitleRow702(sjclForm07.getTitle());
				form.setFyjsRow701(sjclForm07.getFyjs());
				form.setFyjsRow702(sjclForm07.getFyjs());
				form.setFsRow701(sjclForm07.getYjs()+sjclForm07.getFyjs());
				form.setFsRow702(sjclForm07.getYjs()+sjclForm07.getFyjs());
				if("0".equals(sjclForm07.getSfdz()))
				{
					form.setSfdzRow701("√");
					form.setSfdzRow702("√");
				}else{
					form.setSfdzRow701("√");
					form.setSfdzRow702("√");
				}
				
				YwComSjcllistForm sjclForm08=sjclList.get(7);
				form.setTitleRow801(sjclForm08.getTitle());
				form.setTitleRow802(sjclForm08.getTitle());
				form.setFyjsRow801(sjclForm08.getFyjs());
				form.setFyjsRow802(sjclForm08.getFyjs());
				form.setFsRow801(sjclForm08.getYjs()+sjclForm08.getFyjs());
				form.setFsRow802(sjclForm08.getYjs()+sjclForm08.getFyjs());
				if("0".equals(sjclForm08.getSfdz()))
				{
					form.setSfdzRow801("√");
					form.setSfdzRow802("√");
				}else{
					form.setSfdzRow801("√");
					form.setSfdzRow802("√");
				}
				
				YwComSjcllistForm sjclForm09=sjclList.get(8);
				form.setTitleRow901(sjclForm09.getTitle());
				form.setTitleRow902(sjclForm09.getTitle());
				form.setFyjsRow901(sjclForm09.getFyjs());
				form.setFyjsRow902(sjclForm09.getFyjs());
				form.setFsRow901(sjclForm09.getYjs()+sjclForm09.getFyjs());
				form.setFsRow902(sjclForm09.getYjs()+sjclForm09.getFyjs());
				if("0".equals(sjclForm09.getSfdz()))
				{
					form.setSfdzRow901("√");
					form.setSfdzRow902("√");
				}else{
					form.setSfdzRow901("√");
					form.setSfdzRow902("√");
				}
				
				YwComSjcllistForm sjclForm10=sjclList.get(9);
				form.setTitleRow1001(sjclForm10.getTitle());
				form.setTitleRow1002(sjclForm10.getTitle());
				form.setFyjsRow1001(sjclForm10.getFyjs());
				form.setFyjsRow1002(sjclForm10.getFyjs());
				form.setFsRow1001(sjclForm10.getYjs()+sjclForm10.getFyjs());
				form.setFsRow1002(sjclForm10.getYjs()+sjclForm10.getFyjs());
				if("0".equals(sjclForm10.getSfdz()))
				{
					form.setSfdzRow1001("√");
					form.setSfdzRow1002("√");
				}else{
					form.setSfdzRow1001("√");
					form.setSfdzRow1002("√");
				}
				
				YwComSjcllistForm sjclForm11=sjclList.get(10);
				form.setTitleRow1101(sjclForm11.getTitle());
				form.setTitleRow1102(sjclForm11.getTitle());
				form.setFyjsRow1101(sjclForm11.getFyjs());
				form.setFyjsRow1102(sjclForm11.getFyjs());
				form.setFsRow1101(sjclForm11.getYjs()+sjclForm11.getFyjs());
				form.setFsRow1102(sjclForm11.getYjs()+sjclForm11.getFyjs());
				if("0".equals(sjclForm11.getSfdz()))
				{
					form.setSfdzRow1101("√");
					form.setSfdzRow1102("√");
				}else{
					form.setSfdzRow1101("√");
					form.setSfdzRow1102("√");
				}
				if(sjclList.size()>11){
					YwComSjcllistForm sjclForm12=sjclList.get(11);
					form.setTitleRow1201(sjclForm12.getTitle());
					form.setTitleRow1202(sjclForm12.getTitle());
					form.setFyjsRow1201(sjclForm12.getFyjs());
					form.setFyjsRow1202(sjclForm12.getFyjs());
					form.setFsRow1201(sjclForm12.getYjs()+sjclForm12.getFyjs());
					form.setFsRow1202(sjclForm12.getYjs()+sjclForm12.getFyjs());
					if("0".equals(sjclForm12.getSfdz()))
					{
						form.setSfdzRow1201("√");
						form.setSfdzRow1202("√");
					}else{
						form.setSfdzRow1201("√");
						form.setSfdzRow1202("√");
					}
					
					YwComSjcllistForm sjclForm13=sjclList.get(12);
					form.setTitleRow1301(sjclForm13.getTitle());
					form.setTitleRow1302(sjclForm13.getTitle());
					form.setFyjsRow1301(sjclForm13.getFyjs());
					form.setFyjsRow1302(sjclForm13.getFyjs());
					form.setFsRow1301(sjclForm13.getYjs()+sjclForm13.getFyjs());
					form.setFsRow1302(sjclForm13.getYjs()+sjclForm13.getFyjs());
					if("0".equals(sjclForm13.getSfdz()))
					{
						form.setSfdzRow1301("√");
						form.setSfdzRow1302("√");
					}else{
						form.setSfdzRow1301("√");
						form.setSfdzRow1302("√");
					}
					
					YwComSjcllistForm sjclForm14=sjclList.get(13);
					form.setTitleRow1401(sjclForm14.getTitle());
					form.setTitleRow1402(sjclForm14.getTitle());
					form.setFyjsRow1401(sjclForm14.getFyjs());
					form.setFyjsRow1402(sjclForm14.getFyjs());
					form.setFsRow1401(sjclForm14.getYjs()+sjclForm14.getFyjs());
					form.setFsRow1402(sjclForm14.getYjs()+sjclForm14.getFyjs());
					if("0".equals(sjclForm14.getSfdz()))
					{
						form.setSfdzRow1401("√");
						form.setSfdzRow1402("√");
					}else{
						form.setSfdzRow1401("√");
						form.setSfdzRow1402("√");
					}
					
					YwComSjcllistForm sjclForm15=sjclList.get(14);
					form.setTitleRow1501(sjclForm15.getTitle());
					form.setTitleRow1502(sjclForm15.getTitle());
					form.setFyjsRow1501(sjclForm15.getFyjs());
					form.setFyjsRow1502(sjclForm15.getFyjs());
					form.setFsRow1501(sjclForm15.getYjs()+sjclForm15.getFyjs());
					form.setFsRow1502(sjclForm15.getYjs()+sjclForm15.getFyjs());
					if("0".equals(sjclForm15.getSfdz()))
					{
						form.setSfdzRow1501("√");
						form.setSfdzRow1502("√");
					}else{
						form.setSfdzRow1501("√");
						form.setSfdzRow1502("√");
					}
					
					YwComSjcllistForm sjclForm16=sjclList.get(15);
					form.setTitleRow1601(sjclForm16.getTitle());
					form.setTitleRow1602(sjclForm16.getTitle());
					form.setFyjsRow1601(sjclForm16.getFyjs());
					form.setFyjsRow1602(sjclForm16.getFyjs());
					form.setFsRow1601(sjclForm16.getYjs()+sjclForm16.getFyjs());
					form.setFsRow1602(sjclForm16.getYjs()+sjclForm16.getFyjs());
					if("0".equals(sjclForm16.getSfdz()))
					{
						form.setSfdzRow1601("√");
						form.setSfdzRow1602("√");
					}else{
						form.setSfdzRow1601("√");
						form.setSfdzRow1602("√");
					}
				}
//				YwComSjcllistForm sjclForm17=sjclList.get(16);
//				form.setTitleRow1701(sjclForm17.getTitle());
//				form.setTitleRow1702(sjclForm17.getTitle());
//				form.setFyjsRow1701(sjclForm17.getFyjs());
//				form.setFyjsRow1702(sjclForm17.getFyjs());
//				form.setFsRow1701(sjclForm17.getYjs()+sjclForm17.getFyjs());
//				form.setFsRow1702(sjclForm17.getYjs()+sjclForm17.getFyjs());
//				if("0".equals(sjclForm17.getSfdz()))
//				{
//					form.setSfdzRow1701("");
//					form.setSfdzRow1702("");
//				}else{
//					form.setSfdzRow1701("√");
//					form.setSfdzRow1702("√");
//				}
				
				
		}
        
        
		return form;
	}
	
	public List getListById(Long id){
        String sql="select * from SHLJ_CZ_V1 where id="+id;
        return csshljCzDao.getListById(sql);
    }
	
	/**
	 * 修改方法
	 * @param form
	 * @param tablename
	 */
	public void doUpdate(CsshljCzForm form,String tablename) {
		StringBuffer sb=new StringBuffer(" update "+tablename+" set ");
		sb.append(" fzjg='"+form.getFzjg()+"',");
		sb.append(" projectname='"+form.getProjectName()+"',");
		sb.append(" xkcontent='"+form.getXkContent()+"',");
		sb.append(" fzh='"+form.getZsbh()+"',");//原来是zsbh证书编号现在修改为发证号
//		sb.append(" startYear='"+form.getStartYear()+"',");//已经不需要保存
//		sb.append(" startMonth='"+form.getStartMonth()+"',");
//		sb.append(" startDay='"+form.getStartDay()+"',");
//		sb.append(" endYear='"+form.getEndYear()+"',");
//		sb.append(" endMonth='"+form.getEndMonth()+"',");
//		sb.append(" endDay='"+form.getEndDay()+"',");
//		sb.append(" fzYear='"+form.getFzYear()+"',");
//		sb.append(" fzMonth='"+form.getFzMonth()+"',");
//		sb.append(" fzDay='"+form.getFzDay()+"',");
		sb.append(" jdTel='"+form.getJdTel()+"',");
		//加了字段加入修改方法
//		if(StringUtils.isNotBlank(form.getStartYear()) &&
//			StringUtils.isNotBlank(form.getStartMonth()) && 
//			StringUtils.isNotBlank(form.getStartDay())){
//			  sb.append(" jysjsstr='"+form.getStartYear()+"年"+form.getStartMonth()+
//					"月"+form.getStartDay()+"日"+"',");//经营开始时间
//		}
//		if(StringUtils.isNotBlank(form.getEndYear()) &&
//				StringUtils.isNotBlank(form.getEndMonth()) && 
//				StringUtils.isNotBlank(form.getEndDay())){
//				  sb.append(" jysjestr='"+form.getEndYear()+"年"+form.getEndMonth()+
//						"月"+form.getEndDay()+"日"+"',");//经营结束时间
//	    }
//		if(StringUtils.isNotBlank(form.getFzYear()) &&
//				StringUtils.isNotBlank(form.getFzMonth()) && 
//				StringUtils.isNotBlank(form.getFzDay())){
//				  sb.append(" fzsjstr='"+form.getFzYear()+"年"+form.getFzMonth()+
//						"月"+form.getFzDay()+"日"+"',");//发证日期
//		}
		sb.append(" xmmc='"+form.getProjectName()+"' ");//项目名称
		
		sb.append(" where id="+form.getId());
		int i=this.csshljCzDao.updateDateBase(sb.toString());
	}

	public void doUpdateFb(CsshljCzForm form, String tablename) {
		StringBuffer sb=new StringBuffer(" update "+tablename+" set ");
		sb.append(" fzjg='"+form.getFzjg()+"',");
		sb.append(" projectname='"+form.getProjectName()+"',");
		sb.append(" xkcontent='"+form.getXkContent()+"',");
		sb.append(" fzh='"+form.getZsbh()+"',");//原来是zsbh证书编号现在修改为发证号
//		sb.append(" startYear='"+form.getStartYear()+"',");
//		sb.append(" startMonth='"+form.getStartMonth()+"',");
//		sb.append(" startDay='"+form.getStartDay()+"',");
//		sb.append(" endYear='"+form.getEndDay()+"',");
//		sb.append(" endMonth='"+form.getEndMonth()+"',");
//		sb.append(" endDay='"+form.getEndDay()+"',");
//		sb.append(" fzYear='"+form.getFzYear()+"',");
//		sb.append(" fzMonth='"+form.getFzMonth()+"',");
//		sb.append(" fzDay='"+form.getFzDay()+"',");
		sb.append(" jdTel='"+form.getJdTel()+"',");
		//副本开始
		sb.append(" address='"+form.getAddress()+"',");
		if(StringUtils.isNotBlank(form.getFzrq())){
			sb.append(" fzrq='"+form.getFzrq()+"',");
		}
		sb.append(" zczb='"+form.getZczb()+"',");
		sb.append(" qywszb='"+form.getQywszb()+"',");
		sb.append(" qygrzb='"+form.getQygrzb()+"',");
		sb.append(" qygyzb='"+form.getQygyzb()+"',");
		sb.append(" qyhzzb='"+form.getQyhzzb()+"',");
		sb.append(" qylx='"+form.getQylx()+"',");
		sb.append(" qymanagerZc='"+form.getQyManagerZc()+"',");
		sb.append(" qymanagerzw='"+form.getQyMangerZw()+"',");
		sb.append(" qyManager='"+form.getQyManager()+"',");
		sb.append(" fddbrZw='"+form.getFddbrZw()+"',");
		sb.append(" frdb='"+form.getFddbr()+"',");
		sb.append(" fddbrZc='"+form.getFddbrZc()+"',");
		//加了字段加入修改方法
//		if(StringUtils.isNotBlank(form.getStartYear()) &&
//			StringUtils.isNotBlank(form.getStartMonth()) && 
//			StringUtils.isNotBlank(form.getStartDay())){
//			  sb.append(" jysjsstr='"+form.getStartYear()+"年"+form.getStartMonth()+
//					"月"+form.getStartDay()+"日"+"',");//经营开始时间
//		}
//		if(StringUtils.isNotBlank(form.getEndYear()) &&
//				StringUtils.isNotBlank(form.getEndMonth()) && 
//				StringUtils.isNotBlank(form.getEndDay())){
//				  sb.append(" jysjestr='"+form.getEndYear()+"年"+form.getEndMonth()+
//						"月"+form.getEndDay()+"日"+"',");//经营结束时间
//	    }
//		if(StringUtils.isNotBlank(form.getFzYear()) &&
//				StringUtils.isNotBlank(form.getFzMonth()) && 
//				StringUtils.isNotBlank(form.getFzDay())){
//				  sb.append(" fzsjstr='"+form.getFzYear()+"年"+form.getFzMonth()+
//						"月"+form.getFzDay()+"日"+"',");//发证日期
//		}
		sb.append(" xmmc='"+form.getProjectName()+"' ");//项目名称
		sb.append(" where id="+form.getId());
		int i=this.csshljCzDao.updateDateBase(sb.toString());
		
	}
	
	

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.csshlj.service.ICsshljCzService#checkData(java.lang.String, java.lang.Long, java.lang.Long)
	 */
	public boolean checkData(String templateCode,String filename, Long ajid, Long qyid) {
		 Long tempId=getTempId(templateCode, filename);
	        //查询上传的材料
	        //List<YwComSjcl>  sjcls=this.ywComSjclService.getFormByAjId(Long.valueOf(ajid),tempId);
	        List<YwComSjclForm> sjclTpl = this.getFiles(qyid, ajid, templateCode);
	        if(!hasFile(tempId,sjclTpl)){
	            return false;
	        }else{
	            return true;
	        }
	}
    /**
     * 返回模版id
     * @param templateCode 模版材料名称
     * @return
     */
    private Long getTempId(String templateCode,String filename){
        //查询材料模版
        List<YwComSjcllist> sjcllists = ywComSjcllistService.getYwComSjcls(templateCode);
        Long tempId=0l;
        for(YwComSjcllist entity:sjcllists){
            String title=entity.getTitle();
            if(title.indexOf(filename)>=0){
                tempId=entity.getId();
                break;
            }
        }
        return tempId;
    }
    /**
     * 获取所有的收件材料
     * @param qyid
     * @param ajid
     * @param templateCode
     * @return
     */
    private List<YwComSjclForm> getFiles(Long qyid,Long ajid,String templateCode){
        YwComSjclForm fkForm = new YwComSjclForm();
        fkForm.setFkid(qyid);
        fkForm.setAjid(ajid);
        fkForm.setTplcode(templateCode);
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        return sjclTpl;
    }
    
    /**
     * 是否存在文件
     * @return
     */
    private boolean hasFile(Long tempId, List<YwComSjclForm> list){
        String filenames="";
        if(list!=null && list.size()>0){
            for(YwComSjclForm entity:list){
                    if(String.valueOf(entity.getTplid()).equals(String.valueOf(tempId))){
                        String filename=entity.getFilename();
                        if(StringUtils.isNotBlank(filename)){
                            filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                            if("xls".equals(filename)){
                                filenames=filename;
                                break;
                            }
                        }
                    }
               }
            }
           if(StringUtils.isNotBlank(filenames))  return true;
        
        return false;
    }

   /*
    * (non-Javadoc)
    * @see com.augurit.gzcgwsp.csshlj.service.ICsshljCzService#importData(com.augurit.gzcgwsp.csshlj.web.form.CsshljCzForm, java.util.Map)
    */
	public String importData(CsshljCzForm form, Map map) {
		String result="";
		String contextpath=(String) map.get("contextPath");
		Long id=(Long) map.get("id");
		String qybh = (String) map.get("qybh");
        //查询上传的材料
        String filepath=this.getSqbFilePath(map);
        filepath=(contextpath+filepath).replace("/", File.separator);
        File file=new File(filepath);
        if(StringUtils.isNotBlank(filepath)){
        	 Workbook wb;
        	  try {
				wb = Workbook.getWorkbook(file);
			    if(isCorrectFile(wb,1)){
                  Sheet[] sheets=wb.getSheets();
                  result+=this.importInfoData(sheets[2],id);
                  
                  InputStream is=new FileInputStream(filepath);
                  HSSFWorkbook hssfworkbook = new HSSFWorkbook(is);// 将is流实例到
                  csshljclcService.parseYwCsshljclc(hssfworkbook.getSheetAt(3),Long.parseLong(qybh), id);
                  ccljclcService.parseYwCcljclc(hssfworkbook.getSheetAt(4),Long.parseLong(qybh), id);
			   }
	        } catch (Exception e) {
				  result+="收件材料没有符合条件的文件\n";
		    }
        }else{
            result+="收件材料没有符合条件的文件\n";
            
        }
       
		return null;
	}
	
	 /**
     * 功能:导入 处置服务行政许可申请书导入模板.xls 入库操作<br>
     * 参数:<br>
     * 返回:<br>
     * 作者:庄国坤<br>
     * 说明:<br>
     */
    public String importInfoData(Sheet sheet,Long id) throws Exception {
        String rt="";
        String hql=" from "+Csshljcz.class.getName()+" where id="+id;
        try{
        Csshljcz obj=(Csshljcz) this.csshljCzDao.find(hql).get(0);
        //解释sheet
        int offsetRow = 0, offsetCol=0,row = 0;//如果行列有变化,只需修改此偏移量即可.by zhgk 2012/12/17
        row=1+offsetRow;
        //企业名称
        String companyName = ExcelUtil.readData(sheet, offsetCol + 1,
                row);
        String orgCodeName = ExcelUtil.readData(sheet, offsetCol + 3,
                row);
        
        row++;
        String bgzs = ExcelUtil.readData(sheet, offsetCol + 1,
               row);
        
        row++;
        //法人代表
        String lawPerson = ExcelUtil.readData(sheet, offsetCol + 1,
               2);
        //营业执照注册号
        String regCode = ExcelUtil.readData(sheet, offsetCol + 3,
                2);
        
        row++;
        //联系电话
        String telphone = ExcelUtil.readData(sheet, offsetCol + 1,
               3);
        
        row++;
        //注册资本
        String regMoney = ExcelUtil.readData(sheet, offsetCol + 1,
               4); 
        
        row++;
        //验资证明
        String yzzm = ExcelUtil.readData(sheet, offsetCol + 1,
               5); 

        
        //传真号
        String faxCode = ExcelUtil.readData(sheet, offsetCol + 1,
               row); 

        row++;
        //电子信箱
        String email = ExcelUtil.readData(sheet, offsetCol + 1,
               row); 
        //邮政编码
        String postCode = ExcelUtil.readData(sheet, offsetCol + 3,
                row); 
        
        row++;
        //委托代理人 
        String agentMan = ExcelUtil.readData(sheet, offsetCol + 1,
               row);
        
        row++;
        //委托代理人联系电话
        String agentManTel = ExcelUtil.readData(sheet, offsetCol + 1,
               row); 
        row++;
//      委托代理人移动电话
        String agentManMobile = ExcelUtil.readData(sheet, offsetCol + 1,
               row); 
        row++;
//      委托代理人电子信箱
        String agentManEmail = ExcelUtil.readData(sheet, offsetCol + 1,
               row); 
        //obj.setQymc(companyName);
        //Csshljcz.java对象字段不齐.
      //  obj.set组织机构代码();
        if(StringUtils.isNotBlank(yzzm)){
        	yzzm=yzzm.replace("万元", "").replace("附件2", "").replace("(", "");
        	yzzm=yzzm.replace("（", "").replace(")", "").replace("）", "").trim();
        }
        
        if(StringUtils.isNotBlank(regMoney)){
        	regMoney=regMoney.replace("万元", "");
        	if(regMoney.indexOf("(")>0){
        		regMoney=regMoney.trim().substring(0,regMoney.indexOf("("));
        	}
        	if(regMoney.indexOf("（")>0){
        		regMoney=regMoney.trim().substring(0,regMoney.indexOf("（"));
        	}
        	
        }
        if(StringUtils.isNotBlank(agentMan)){
 	  	   agentMan=agentMan.replace("持法人代表委托书","");
 	  	   agentMan=agentMan.replace("(附件3)", "").trim();
 		
   }
        obj.setZzjgdm(orgCodeName);//组织机构代码
        obj.setFrdb(lawPerson); //法人代表
       // obj.setBgzs(bgzs);//办公住所
        obj.setYyzzzch(regCode);//营业执照注册号
        obj.setLxdh(telphone); //联系电话
        obj.setZczb(regMoney);//注册资本
       // obj.setXmmc(xmmc);//项目名称
//        obj.setYzzm(yzzm);
        obj.setEmail(email);//电子邮件
        obj.setCz(faxCode);//传真
        obj.setEmail(email);//代理人
        obj.setYzbm(postCode);//邮编
        obj.setWtsfj(agentMan);//代理人
        obj.setDllxdh(agentManTel);//代理联系电话
        obj.setDlrdzyx(agentManEmail);//代理人电子邮箱
        obj.setDlryddh(agentManMobile);//代理人移动电话
        
        csshljCzDao.save(obj); //请修改此处DAO调用  
        
        //导入企业信息数据
        
        if(StringUtils.isNotBlank(obj.getQybh())){
  			YwComQyxxForm form=ywComQyxxService.get(Long.valueOf(obj.getQybh()));
  			form.setSqdw(obj.getSqdw());
  			form.setFddbr(obj.getFrdb());
  			form.setCz(faxCode);
  			form.setZczj(regMoney);
  			form.setEmail(obj.getEmail());
  			form.setPost(obj.getYzbm());
  			form.setQyphone(telphone);
  			form.setAddress(obj.getBgzs());
  			ywComQyxxService.updateYwComQyxx(form);
  		}
        
        //处理城市生活垃圾处置专业人员 子表
        //读取子数据
        row=offsetRow+15;//从第16行开始保存本信息
        long mId=obj.getId();//主表ID,假使前面保存正确并已获取到了ID
        
        //开始导入人员
        log.info("导入的行数："+sheet.getRows());
        String sql=" from Person where czid="+mId;
        List<Person> persons=personDao.find(sql);
        for(Person person:persons){
        	this.personDao.delete(person);
        }
        for(int i=14;i<sheet.getRows();i++){
        	String name=ExcelUtil.readData(sheet,0,i);
            String xueli=ExcelUtil.readData(sheet,1,i);//学历
            String zhuanYe=ExcelUtil.readData(sheet,2,i);
            if(StringUtils.isNotBlank(name)){
            	   Person obj2=new Person();
                   obj2.setCzid(mId);
                   obj2.setName(name);
                   obj2.setXl(xueli);
                   obj2.setZy(zhuanYe);
                   personDao.save(obj2); //请修改
            }
        }
     
        }catch(Exception ee){
            rt="导入 处置服务行政许可申请书导入模板 Excel出错"+ee;
        }
        return rt;
    }
    
    /**
     * 获取申请表文件
     * @param sjcls
     * @return
     */
    public String getSqbFilePath(Map map){
    	 String templateCode=(String) map.get("templateCode");
         Long ajid=(Long) map.get("id");
         String contextpath=(String) map.get("contextPath");
         String fileName=(String) map.get("filename");
         Long tempId=getTempId(templateCode,fileName);
        List<YwComSjclForm> sjcls = getFiles(null, Long.valueOf(ajid), templateCode);
        String filepath="";
        Workbook wb;
        try {
            for(YwComSjclForm entity:sjcls){
                if(String.valueOf(entity.getTplid()).equals(String.valueOf(tempId))){
                    filepath=entity.getFilepath();
                    if(StringUtils.isNotBlank(filepath)){
                        String[] paths=filepath.split(",");
                    for(String path:paths){
                            path=path.replace("/", File.separator);
                            File file=new File(contextpath+path);
                            String name=entity.getFilename();
                            if(StringUtils.isNotBlank(name)){
                                for(String filename:name.split(",")){
                                filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                                if(!"xls".equals(filename)){
                                    continue;
                                }
                                if(!file.exists()){
                                    continue;
                                }else{
                                    wb = Workbook.getWorkbook(file);
                                    if(isCorrectFile(wb,1)){
                                        return path;
                                    }
                               }
                              }
                            }
                       
                        }
                    }
                }
            }
        } catch (Exception e) {
           e.printStackTrace();
        }
        return filepath;
    }
    
    /**
     * 是否是正确的文件格式
     * @param wb
     * @return
     */
    private boolean isCorrectFile(Workbook wb,int sheetsize){
        Sheet[] sheets=wb.getSheets();
        if(sheets.length>=sheetsize)
            return true;
        else
            return false;
    }

    /**
     * 打印写数据
     */
	public String writeXkzData(CsshljCzForm form, String context,String inpath) {
		   inpath=inpath.replace("/", File.separator);
	        String tempOutPath="Templates/csshlj/outfile";
	        String fileName = GetFileName.getFileName("xls");
	        tempOutPath=this.buildPath(tempOutPath, fileName);
	        String outfilepath=context+"/"+tempOutPath;
	        outfilepath=outfilepath.replace("/", File.separator);
	        String outdir=outfilepath.substring(0,outfilepath.lastIndexOf(File.separator));
	        File file=new File(outdir);
	        if(!file.exists()){
	            file.mkdirs();
	        }
	        try {
				FileUtil.delDir(outdir, fileName);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	        writerDataExcel(inpath,outfilepath,form);
	        return tempOutPath;
	}

	/**
	 * 写入副本数据
	 */
	public String writeFbData(CsshljCzForm form, String context, String inpath) {
		inpath=inpath.replace("/", File.separator);
        String tempOutPath="Templates/csshlj/outfile/xkzfb/outfile";
        String fileName = GetFileName.getFileName("xls");
        tempOutPath=this.buildPath(tempOutPath, fileName);
        String outfilepath=context+"/"+tempOutPath;
        outfilepath=outfilepath.replace("/", File.separator);
        String outdir=outfilepath.substring(0,outfilepath.lastIndexOf(File.separator));
        File file=new File(outdir);
        if(!file.exists()){
            file.mkdirs();
        }
        try {
			FileUtil.delDir(outdir, fileName);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        writerFbDataExcel(inpath,outfilepath,form);
        
		return tempOutPath;
	}
    /**
     * 复制模版写入数据
     * @param inpath
     * @param outfilepath
     * @param form
     */
    private void writerDataExcel(String inpath, String outfilepath,
    		CsshljCzForm form) {
        WritableWorkbook wwb;
        try {
            wwb = ExcelUtil.createWorkbookByOld(inpath, outfilepath);
            WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
            copyZsData(wsOne,form);
            wwb.write();
            wwb.close();
        } catch (Exception e) {
            log.error("写文件错误"+e);
        } 
    }
    /**
     * 复制副本写入数据
     * @param inpath
     * @param outfilepath
     * @param form
     */
    private void writerFbDataExcel(String inpath, String outfilepath,
    		CsshljCzForm form) {
        WritableWorkbook wwb;
        try {
            wwb = ExcelUtil.createWorkbookByOld(inpath, outfilepath);
            WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
            copyFbData(wsOne,form);
            wwb.write();
            wwb.close();
        } catch (Exception e) {
            log.error("写文件错误"+e);
        } 
    }

    /**
     * 写数据
     * @param wsOne
     * @param form
     */
    private void copyFbData(WritableSheet wsOne, CsshljCzForm form) {
    	 try {
    		 WritableCellFormat wcf=ExcelUtil.createCellFormat("宋体", 14, Alignment.LEFT, VerticalAlignment.CENTRE,false);
			 WritableCellFormat wcf1=ExcelUtil.createCellFormat("宋体", 14, Alignment.LEFT, VerticalAlignment.CENTRE,true);
			 WritableCellFormat wcf2=ExcelUtil.createCellFormat("宋体", 12, Alignment.CENTRE, VerticalAlignment.CENTRE,false);
			 WritableCellFormat wcf3=ExcelUtil.createCellFormat("宋体", 14, Alignment.LEFT, VerticalAlignment.TOP,false);
			 WritableCellFormat wcfdate=ExcelUtil.createCellFormat("宋体", 8, Alignment.CENTRE, VerticalAlignment.CENTRE,false);
			 wcf.setLocked(false);
			 wcf1.setLocked(false);
			 wcf2.setLocked(false);
			 wcf3.setLocked(false);
			 wcfdate.setLocked(false);
			 ExcelUtil.modiStrCell2(wsOne, 1, 1, form.getSqdw()==null?"":form.getSqdw(), wcf);//申请单位
			 ExcelUtil.modiStrCell2(wsOne,1, 2, form.getAddress()==null?"":form.getAddress(), wcf1);//地址
			 ExcelUtil.modiStrCell2(wsOne,1, 3, form.getYyzch()==null?"":form.getYyzch(), wcf);//营业执照注册号
			 ExcelUtil.modiStrCell2(wsOne,1, 4, form.getZczb()==null?"":form.getZczb(), wcf);//注册资本
			 ExcelUtil.modiStrCell2(wsOne,1, 6, form.getFddbr()==null?"":form.getFddbr(), wcf2);//法定代表人
			 ExcelUtil.modiStrCell2(wsOne,8, 6, form.getFddbrZw()==null?"":form.getFddbrZw(), wcf2);//职务
			 ExcelUtil.modiStrCell2(wsOne,17, 6, form.getFddbrZc()==null?"":form.getFddbrZc(), wcf2);//职称
			 ExcelUtil.modiStrCell2(wsOne,1, 8, form.getQyManager()==null?"":form.getQyManager(), wcf2);//企业负责人
			 ExcelUtil.modiStrCell2(wsOne,8, 8,form.getQyManagerZw()==null?"":form.getQyManagerZw(), wcf2);//职务
			 ExcelUtil.modiStrCell2(wsOne,17, 8,form.getQyManagerZc()==null?"":form.getQyManagerZc(), wcf2);//职称
			 if("1".equals(form.getQylx())){//内资企业
				  ExcelUtil.modiStrCell2(wsOne,1, 10,"√", wcf2);//企业类型内资
			 }else if ("2".equals(form.getQylx())){
				  ExcelUtil.modiStrCell2(wsOne,9, 10,"√", wcf2);//企业类型 港澳台
			 } else if ("2".equals(form.getQylx())){
			  ExcelUtil.modiStrCell2(wsOne,1, 11,"√", wcf2);//企业类型 外商
			}else if ("2".equals(form.getQylx())){
			  ExcelUtil.modiStrCell2(wsOne,9, 11,"√", wcf2);//企业类型 台资
			}
      
			 ExcelUtil.modiStrCell2(wsOne,2, 14, form.getQygyzb()==null?"":form.getQygyzb(), wcf2);//企业资本构成比例 国有
			 ExcelUtil.modiStrCell2(wsOne,11, 14, form.getQygrzb()==null?"":form.getQygrzb(), wcf2);//企业资本构成比例 个人
			 ExcelUtil.modiStrCell2(wsOne,2, 15, form.getQywszb()==null?"":form.getQywszb(), wcf2);//企业资本构成比例 外商
			 ExcelUtil.modiStrCell2(wsOne,13, 15, form.getQyhzzb()==null?"":form.getQyhzzb(), wcf2);//企业资本构成比例 港澳
			 
			 ExcelUtil.modiStrCell2(wsOne,1, 18, form.getXkContent()==null?"":form.getXkContent(), wcf2);//许可正内容
			 
			 ExcelUtil.modiStrCell2(wsOne,1, 20, form.getFzjg()==null?"":form.getFzjg(), wcf2);//许可发证机关
			 
			 ExcelUtil.modiStrCell2(wsOne,15, 21, form.getJdTel()==null?"":form.getJdTel(), wcf2);//监督电话
			 
			 ExcelUtil.modiStrCell2(wsOne,1, 23, form.getStartYear()==null?"":form.getStartYear(), wcf2);//许可正发证日期

			 ExcelUtil.modiStrCell2(wsOne,4, 23, form.getStartMonth()==null?"":form.getStartMonth(), wcf2);//许可正发证日期

			 ExcelUtil.modiStrCell2(wsOne,7, 23, form.getStartDay()==null?"":form.getStartDay(), wcf2);//许可正发证日期
      
			 ExcelUtil.modiStrCell2(wsOne,10, 23, form.getEndYear()==null?"":form.getEndYear(), wcf2);//许可正发证日期

			 ExcelUtil.modiStrCell2(wsOne,13, 23, form.getEndMonth()==null?"":form.getEndMonth(), wcf2);//许可正发证日期

			 ExcelUtil.modiStrCell2(wsOne,16, 23, form.getEndDay()==null?"":form.getEndDay(), wcf2);//许可正发证日期
      
			 ExcelUtil.modiStrCell2(wsOne,1, 25, form.getFzrq()==null?"":form.getFzrq(), wcf2);//许可正发证日期
			 ExcelUtil.modiStrCell2(wsOne,18, 25, form.getZsbh()==null?"":form.getZsbh(), wcf2);//许可正发证日期
		} catch (Exception e) {
			e.printStackTrace();
		} 
         
        
    }
    
    /**
     * 写证书数据
     * @param wsOne
     * @param form
     */
    private void copyZsData(WritableSheet wsOne, CsshljCzForm form) {
       
        try {
            WritableCellFormat wcf=ExcelUtil.createCellFormat("宋体", 20, Alignment.LEFT, VerticalAlignment.CENTRE,false);
            WritableCellFormat wcf5=ExcelUtil.createCellFormat("宋体", 20, Alignment.RIGHT, VerticalAlignment.CENTRE,false);
            WritableCellFormat wcf1=ExcelUtil.createCellFormat("宋体", 20, Alignment.LEFT, VerticalAlignment.CENTRE,true);
            WritableCellFormat wcf2=ExcelUtil.createCellFormat("宋体", 12, Alignment.LEFT, VerticalAlignment.CENTRE,false);
            WritableCellFormat wcf3=ExcelUtil.createCellFormat("宋体", 12, Alignment.CENTRE, VerticalAlignment.CENTRE,false);
            WritableCellFormat wcf4=ExcelUtil.createCellFormat("宋体", 12, Alignment.RIGHT, VerticalAlignment.CENTRE,false);
           // ExcelUtil.modiStrCell2(wsOne, 4,14, form.getZsbh()==null?"":form.getZsbh(), wcf);//编号
            ExcelUtil.modiStrCell2(wsOne, 0, 8, form.getSqdw()==null?"":form.getSqdw(), wcf5);//申请单位
            //ExcelUtil.modiStrCell2(wsOne, 10, 16, "广州市城市委员会", wcf);//发证机关
            ExcelUtil.modiStrCell2(wsOne, 3, 20, form.getXkContent()==null?"":form.getXkContent(), wcf);//许可内容
            //ExcelUtil.modiStrCell2(wsOne, 3, 21, form.getProjectName()==null?"":form.getProjectName(), wcf);//项目名称
            ExcelUtil.modiStrCell2(wsOne, 8, 22, form.getZsbh()==null?"":form.getZsbh(), wcf);//证书编号

            ExcelUtil.modiStrCell2(wsOne, 3, 23, form.getStartYear()==null?"":form.getStartYear(), wcf3);//开始-年
            ExcelUtil.modiStrCell2(wsOne, 5, 23, form.getStartMonth()==null?"":form.getStartMonth(), wcf3);//开始-月
            ExcelUtil.modiStrCell2(wsOne, 7, 23, form.getStartDay()==null?"":form.getStartDay(), wcf3);//开始-日
            ExcelUtil.modiStrCell2(wsOne, 10, 23, form.getEndYear()==null?"":form.getEndYear(), wcf3);//结束-年
            ExcelUtil.modiStrCell2(wsOne, 12, 23, form.getEndMonth()==null?"":form.getEndMonth(), wcf3);//结束-月
            ExcelUtil.modiStrCell2(wsOne, 14, 23, form.getEndDay()==null?"":form.getEndDay(), wcf3);//结束-日
            ExcelUtil.modiStrCell2(wsOne, 18, 23, form.getFzYear()==null?"":form.getFzYear(), wcf4);//发证-年
            ExcelUtil.modiStrCell2(wsOne, 20, 23, form.getFzMonth()==null?"":form.getFzMonth(), wcf3);//发证-月
            ExcelUtil.modiStrCell2(wsOne, 22, 23, form.getFzDay()==null?"":form.getFzDay(), wcf3);//发证-日
            ExcelUtil.modiStrCell2(wsOne, 21, 24, form.getJdTel()==null?"":form.getJdTel(), wcf2);//监督电话

            //wcf.setLocked(false);
            //wcf1.setLocked(false);
           // wcf2.setLocked(false);
                 
        } catch (Exception e) {
            e.printStackTrace();
            log.error("写入正式数据出现错误"+e);
        } 
        
    }
    /**
     * 生成后面的文件路径加上年月
     * @param realpath 定义的目录
     * @param fileNamesuffix 文件后缀名
     * @return
     */
    private String buildPath(String realpath,String fileName){
        String dateDir = GetFileName.getDirector();
        return realpath+"/"+dateDir+"/"+fileName;
        
    }
    
    /**
     * 生成证书编号
     */
	public String createZsbh(Map map,Long id) {
		String tablename=(String) map.get("tablename");
		String zsname=(String) map.get("zsname");
		
		String sql= "select * from "+tablename+" where id="+id;
		List list=this.csshljCzDao.getListById(sql);
		if(list!=null && list.size()>0){
			Map dataMap=(Map) list.get(0);
			String zsbh=(String) dataMap.get(zsname);
			String area=(String) map.get("xmqy");
			if(StringUtils.isBlank(zsbh)){
				//开始加入证书编号
				zsbh=this.buildZsbh(tablename, area);
				String updatesql=" update "+tablename+" set fzh='"+zsbh+"' where id="+id;
				csshljCzDao.updateDateBase(updatesql);
			}
			return zsbh;
		}
		
		return null;
	}
	
	/**
	 * 生成证书
	 * @return
	 */
	public String buildZsbh(String tablename,String area){
		if(StringUtils.isBlank(area) || "null".equals(area)){
			area="00";//如果空就是00
		}
		String sql="select count(id) as countnum from "+tablename+" where fzh is not null";
		List list=csshljCzDao.queryForList(sql);
		Map map=(Map) list.get(0);
		String numstr=(String) map.get("countnum");
		if(StringUtils.isNotBlank(numstr)){
			Long num=Long.valueOf(numstr)+1;
			int k=4-String.valueOf(num).length();
			String prefix="";
			for(int i=1;i<=k;i++){
				prefix+="0";
			}
			return area+prefix+String.valueOf(num);
		}else{
			return area+"0001";
		}
	}


	/*
	 * 
	 * @see com.augurit.gzcgwsp.csshlj.service.ICsshljCzService#reSetForm(com.augurit.gzcgwsp.csshlj.web.form.CsshljCzForm)
	 */
	public void reSetForm(Object form) {
		Field[] field = form.getClass().getDeclaredFields();    
        for(int j=0 ; j<field.length ; j++){     
            try {
				String fieldName = field[j].getName();  
				String type = field[j].getGenericType().toString();    
				if(type.equals("class java.lang.String")){   
				    Method m = form.getClass().getMethod("get"+StringUtils.capitalize(fieldName));
				    String value = (String) m.invoke(form);   
				    if(value == null || "null".equals(value)){
				        ReflectionUtils.setFieldValue(form, fieldName, "");
				    }
				}
			} catch (Exception e) {
				log.error("转换异常"+e);
			}
        }
	  }

	/**
	 * 保存信息到企业表
	 */
	public Long saveEnterpriseInfo(String tepcode, Map map) {
		String qyid=(String) map.get("qyid");//企业id
		String sqdw=(String) map.get("sqdw");//申请单位
		String frdb=(String) map.get("frdb");//法人代表
		String yyzzzch=(String) map.get("yyzzzch");//营业注册号码
		String tel=(String) map.get("tel");//联系电话
		String regmoney=(String) map.get("regmoney");//注册资金
		String fax=(String) map.get("fax");//传真
		String email=(String) map.get("email");//电子邮箱
		String post=(String) map.get("post");//邮编
		String address=(String) map.get("address");//办公住所
		String zsbh=(String) map.get("zsbh");//办公住所
		if(StringUtils.isBlank(qyid)){
			YwComQyxxForm form=new YwComQyxxForm();
			form.setSqdw(sqdw);
			form.setFddbr(frdb);
			form.setCz(fax);
			form.setZczj(regmoney);
			form.setEmail(email);
			form.setPost(post);
			form.setTemplateCode(tepcode);
			form.setQyphone(tel);
			form.setAddress(address);
			if(zsbh!=null && zsbh.length()>0){
				form.setZsbh(zsbh);
			}
			return ywComQyxxService.saveEnterpriseInfo(form);
		}
		return null;
	}

	/**
	 * 检查企业是否存在
	 */
	public String checkEnterprise(String qyname, String tempcode) {
		YwComQyxx entity=ywComQyxxService.getEntity(qyname,tempcode);
		if(entity!=null){
			return "exsit";
		}
		return null;
	}
	
	public void save(CsshljCzForm... forms) {
		if(forms != null)
			for(CsshljCzForm form : forms)
				this.save(form);
	}
	
	public void save(CsshljCzForm form) {
		System.out.println("&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&"+form.getQymc());
		
        if (form == null) return;

        Csshljcz entity = new Csshljcz();
        //保存企业信息
        YwComQyxxForm qyxxForm = new YwComQyxxForm();
        if(StringUtils.isEmpty(form.getQybh())){
        	if(StringUtils.isNotBlank(form.getSqdw())){
            	YwComQyxxForm ywComQyxxForm = ywComQyxxService.getFormBySqdw(form.getSqdw(), "shljcz");
            	if(ywComQyxxForm != null){
            		qyxxForm = ywComQyxxForm;
            	}
            }
        }
        YwHwCzConvertor.convertFormToQyxxForm(form, qyxxForm);
        qyxxForm.setTemplateCode("shljcz");
        ywComQyxxService.update(qyxxForm);
        form.setQybh(qyxxForm.getId().toString());
        System.out.println("&&&&&&&&&&&&&&&&&&&&&  qyxxForm = "+qyxxForm.getId());
        
        boolean isNewForm = false;
        if (StringUtils.isBlank(form.getAcceptNo())) {
        	//生成政务中心统一查询编号 开始
			Map<String,String> serialNoAndPasswordMap = null;
        	if("Y".equals(form.getSendNotice())){//网办，1
        		serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("1");
        	}else{//大厅，0
        		serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("0");
        	}
        	String serialNo = serialNoAndPasswordMap.get("serialNo");
        	String password = serialNoAndPasswordMap.get("password");
			form.setSerialNo(serialNo);
			form.setPassword(password);
			form.setAcceptNo(serialNo);
    		//生成政务中心统一查询编号 结束
            isNewForm = true;
        }
        YwHwCzConvertor.convertFormToVo(form, entity);
        System.out.println("&&&&&&&&&&&&&&&&&&&&&  后  "+entity.getSqdw());
        System.out.println("&&&&&&&&&&&&&&&&&&&&&  entity.getId() =  "+entity.getId());
        Session session = csshljCzDao.getSessionFactory().openSession();
        session.save(entity);
        session.flush();
        session.close();
        form.setId(entity.getId());
        
//        if (isNewForm) {
//            sjclService.updateByNewForm(form.getId());
//        }
	}
	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.csshlj.service.ICsshljCzService#toListDdData(org.springside.modules.orm.Page, java.lang.String)
	 */
	/**
	 * 获取待办数据sql
	 * @param username
	 * @return
	 */
	private String getDbdataSql(Map map){
		StringBuffer sb=new StringBuffer();
		String username=(String) map.get("username");
		String startdate=(String)map.get("startdate");
		String qyname=(String)map.get("qyname");
		String enddate=(String)map.get("enddate");
		if(StringUtils.isNotBlank(enddate)){
			String[] enddates=enddate.split("-");
			if(enddates.length==3){
				if(enddates[1].length()<2){//如果日期是2012-5-12这种格式
					enddate=enddates[0]+"-0"+enddates[1]+"-"+enddates[2];
				}
			}
		}
		if(StringUtils.isBlank(enddate)){//如果日期为空就为当前日期
			enddate=DateUtilsEX.dateFormate(new Date(),"yyyy-MM-dd");
			enddate+=" 23:59:59";
		}else if(enddate.length()>=10){
			enddate=enddate.substring(0,10);
			enddate=enddate.substring(0,10)+" 23:59:59";
		}
		if(StringUtils.isBlank(startdate)){//如果日期为空显示3天前的数据
			startdate=DateUtilsEX.getDateByDay(new Date(),-3,"yyyy-MM-dd");
		}else if(startdate.length()>=10){
			startdate=startdate.substring(0,10);
		}
		sb.append("select cz.id,cz.qymc,cz.frdb,cz.lxdh,cz.jysjsstr,cz.jysjestr,cz.zczb,cz.bgzs  from "+Csshljcz.class.getName()+" as cz,");
		sb.append("Jbpm4HistProcinst as proc,WfBusInstance inst,Jbpm4HistTask as task ");
		sb.append(" where cz.id=inst.masterEntityKey and proc.procInstId = inst.procInstId");
		sb.append(" and task.procInstId = proc.procInstId and task.state='"+Task.STATE_OPEN+"'");
		sb.append(" and inst.masterEntity='Csshljcz'");
		sb.append(" and task.assignee='"+username+"'"+" and task.activityName='jbrsj'");
		if(StringUtils.isNotBlank(qyname)){
			sb.append(" and cz.qymc like '%"+qyname+"%'");
		}
		if(StringUtils.isNotBlank(startdate)){
			sb.append(" and task.signTime>=to_date('"+startdate+"','yyyy-mm-dd')");
		}
		if(StringUtils.isNotBlank(enddate)){
			sb.append(" and task.signTime<=to_date('"+enddate+"','yyyy-mm-dd HH24:mi:ss')");
		}
		
		return sb.toString();
	}
	
	
	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.csshlj.service.ICsshljCzService#toListDdData(org.springside.modules.orm.Page, java.lang.String)
	 */
	public void toListDdData(Page page, Map map) {
		String sqyw=(String) map.get("spyw");
		// 建立新的分页对象
		if(StringUtils.isNotBlank(sqyw)){
			if(sqyw.indexOf("处置")>=0){
				Page pg = PageUtils.newPageInstance(page);
				String sql=this.getDbdataSql(map);
				csshljCzDao.findPage(pg, sql);
				List<CsshljCzForm> list = convertToList(pg.getResult());
				PageUtils.copy(pg, list, page);
			}else if(sqyw.indexOf("水域")>=0){
				shljYsService.getDbData(page, map);
				
			}else if(sqyw.indexOf("陆域")>=0){
				smartFormShljqsService.getDbData(page, map);
			}
		}else{//如果为空就默认查陆域
			smartFormShljqsService.getDbData(page, map);
		}
		
//		sb.append("select cz.qymc,cz.zsbh from SHLJ_CZ_V1 cz,JBPM4_HIST_TASK task,");
//		sb.append("JBPM4_HIST_PROCINST proc,WF_BUS_INSTANCE inst where ");
//		sb.append("	task.PROCINST_ = proc.ID_  and proc.ID_ = inst.PROC_INST_ID");
//		sb.append(" and inst.MASTER_ENTITY_KEY = cz.id and task.state_='"+Task.STATE_OPEN);
//		sb.append(" and task.assignee_name='"+username+"' and inst.master_entity='SHLJ_CZ_V1'");	
	}
	
	/**
	 * 导出要待办的内容
	 */
	public String exportExcel(Map map) {
		String sql=this.getDbdataSql(map);
		String sqyw=(String) map.get("spyw");//审批业务
		String path=(String) map.get("contextpath");
		String outFilePath = GetFileName.GetRealPath("Templates/modelTemplate", "xls");
		String outPath=path+File.separator+outFilePath;
		outPath=outPath.replace("/", File.separator);
		String dir=outPath.substring(0,outPath.lastIndexOf(File.separator));
		dir=dir.replace("/", File.separator);
		File dirfile=new File(dir);
		if(!dirfile.exists()){
			dirfile.mkdirs();
		}
		if(sqyw.indexOf("处置")>=0){
			String inFilePath = path+File.separator+"Templates"+File.separator+"exportCsshljCz.xls";
			outPath=outPath.replace("/", File.separator);
			List<Object[]> list=this.csshljCzDao.find(sql);
			writeData(inFilePath, outPath,list,sqyw);
		
		}else if(sqyw.indexOf("水域")>=0){
			String inFilePath = path+File.separator+"Templates"+File.separator+"exportCsshlj.xls";
			List<Object[]> list=shljYsService.getDbDatas(map);
			writeData(inFilePath, outPath,list,sqyw);
			
		}else if(sqyw.indexOf("陆域")>=0){
			String inFilePath = path+File.separator+"Templates"+File.separator+"exportCsshlj.xls";
			List<Object[]> list=smartFormShljqsService.getDbDatas(map);
			writeData(inFilePath, outPath,list,sqyw);
		}
		
	
		String filename=outPath.substring(outPath.lastIndexOf(File.separator)+1,outPath.length());
		try {//删除其他文件
			FileUtil.delDir(dir,filename);
		} catch (IOException e) {
			log.error("删除文件错误"+e);
		}
		return outFilePath;
	}
	
	/**
	 * 写入待办的数据
	 * @param inFilePath
	 * @param outFilePath
	 * @param list
	 */
	private void writeData(String inFilePath, String outFilePath,List<Object[]> list,String sqyw){
		outFilePath=outFilePath.replace("/", File.separator);
		WritableWorkbook wwb;
		try {
		        wwb = ExcelUtil.createWorkbookByOld(inFilePath, outFilePath);
	            WritableSheet ws = ExcelUtil.getWritableSheetByIndex(wwb, 0);
	            WritableFont font = new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.BOLD);
	            WritableCellFormat wcf = new WritableCellFormat();
                wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
                wcf.setAlignment(Alignment.CENTRE);
                wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
                wcf.setWrap(true);
                WritableCellFormat wcf1 = new WritableCellFormat(font,NumberFormats.TEXT);
                wcf1.setBorder(Border.ALL, BorderLineStyle.THIN);
                wcf1.setAlignment(Alignment.LEFT);
                wcf1.setVerticalAlignment(VerticalAlignment.CENTRE); 
                String title="审批单位:（盖章）";
                for(int k=0;k<=85;k++){
                	title+=" ";//空格
                }
                title+=DateUtilsEX.getCurYear()+"年"+DateUtilsEX.getCurMonth()+
                		"月"+DateUtilsEX.getCurDay()+"日";
            	ExcelUtil.modiStrCell2(ws, 0,  1, title, wcf1);
            	
		        for(int i=0;i<list.size();i++){
		            try {
		            	for(Object[] objs:list){
		            		String bgaddr="";//办公住所
		            		String ssaddr="";//设施地址
		            		String jyfw=sqyw;//经营范围
		            		String jyscale="";//经营规模
		            		String frdb="";//法人代表
		            		String sqdw=(String) objs[1];
		    	    		frdb=(String) objs[2];
		    	    		String lxdh=(String) objs[3];
		    	    		String startdate=(String) objs[4];
		    	    		String enddate=(String) objs[5];
		    	    		jyscale=this.getJyscale((Long)objs[0]);
		    	    		if(sqyw.indexOf("水域")>=0 || sqyw.indexOf("陆域")>=0){
		    	    			jyscale=(String)objs[6];
		    	    		}
		    	    	
		    	    		bgaddr=(String) objs[7];
		    	    		ssaddr=(String) objs[7];
		    	    		if(StringUtils.isBlank(startdate)){
		    	    			startdate=DateUtilsEX.getCurYear()+"年"+"    月"+"    日";
		    	    		}else{
		    	    			startdate=startdate.substring(0,4)+"年"+startdate.substring(5,7)+"月"+
		    	    			startdate.substring(8,10)+"日";
		    	    		}
		    	    		if(StringUtils.isBlank(enddate)){
		    	    			enddate=DateUtilsEX.getCurYear()+"年"+"    月"+"    日";
		    	    		}else{
		    	    			enddate=enddate.substring(0,4)+"年"+enddate.substring(5,7)+"月"+
		    	    			enddate.substring(8,10)+"日";
		    	    		}
			              
			                ExcelUtil.modiStrCell2(ws, 0,  i+3, i+1+"", wcf);
			                ExcelUtil.modiStrCell2(ws, 1,  i+3, sqdw, wcf);//法人名称
			                ExcelUtil.modiStrCell2(ws, 2,  i+3, bgaddr, wcf);//办公住所
			                ExcelUtil.modiStrCell2(ws, 3,  i+3, ssaddr, wcf);//设施地址
			                ExcelUtil.modiStrCell2(ws, 4,  i+3, jyfw, wcf);//核准经营范围
			                ExcelUtil.modiStrCell2(ws, 5,  i+3, jyscale, wcf);
			                ExcelUtil.modiStrCell2(ws, 6,  i+3, frdb, wcf);//联系人
			                ExcelUtil.modiStrCell2(ws, 7,  i+3, frdb, wcf);//联系人
			                ExcelUtil.modiStrCell2(ws, 8,  i+3, lxdh, wcf);//联系电话
			                ExcelUtil.modiStrCell2(ws, 9,  i+3, startdate+"~"+enddate, wcf);//
			                ws.setRowView(i+3,600); 
			                i++;
		            	}
		            } catch (Exception e) {
		                log.error("导出数据错误"+e);
		            } 
		        
		    }
		        wwb.write();
				wwb.close();
		    }catch(Exception e){
		    	log.error("导出数据"+e);
		    }
	}
	

	
/**
 * 转换form
 * @param result
 * @return
 */
	private List<CsshljCzForm> convertToList(List<Object[]> result) {
		List<CsshljCzForm> list=new ArrayList<CsshljCzForm>();
	    if(result!=null && result.size()>0){
	    	CsshljCzForm form=null;
	    	for(Object[] objs:result){
	    		form=new CsshljCzForm();
	    		form.setId((Long) objs[0]);
	    		String sqdw=(String) objs[1];//申请单位
	    		String frdb=(String) objs[2];//法人代表
	    		String lxdh=(String) objs[3];//联系电话
	    		String startdate=(String) objs[4];
	    		String enddate=(String) objs[5];
	    		form.setFrdb(frdb==null?"":frdb);//法人代表
	    		form.setSqdw(sqdw==null?"":sqdw);//申请单位
	    		form.setCompanyphone(lxdh==null?"":lxdh);//联系电话
	    		String address=(String)objs[7];//办公住所
	    		form.setFrdb(frdb==null?"":frdb);//法人代表
	    		String jyscale=this.getJyscale((Long) objs[0]);
	    		form.setJyscale(jyscale);
	    		form.setSqdw(sqdw==null?"":sqdw);//申请单位
	    		form.setCompanyphone(lxdh==null?"":lxdh);//联系电话
	    		form.setAddress(address==null?"":address);//办公住所
	    		form.setCbyw("城市生活垃圾经营性处置");
	    		form.setSsAddr(address==null?"":address);//设施地址
	    		if(StringUtils.isBlank(startdate)){
	    			startdate=DateUtilsEX.getCurYear()+"年"+"  月"+"  日";
	    		}
	    		if(StringUtils.isBlank(enddate)){
	    			enddate=DateUtilsEX.getCurYear()+"年"+"  月"+"  日";
	    		}
	    		form.setValidstarttime(startdate);
	    		form.setValidendtime(enddate);
	    		form.setValiddate(startdate+"~~"+enddate);
	    		list.add(form);
	    	}
			
	    }
		return list;
	}
	
	/**
	 * 获取经营规模
	 * @param czid
	 * @return
	 */
	private String getJyscale(Long czid){
		String hql=" from Ccljclc where czid="+czid;
		String hqls=" from Csshljclc where czid="+czid;
		String jyscal="";
		List<CcljclcForm> datas=this.ccljclcService.getListByCzId(czid);
		List<CsshljclcForm> data=this.csshljclcService.getListByCzId(czid);
		for(CcljclcForm form:datas){
			jyscal+=form.getClnl()+"吨/天"+"、";
		}
		for(CsshljclcForm form:data){
			jyscal+=form.getClnl()+"吨/天"+"、";
		}
		if(StringUtils.isNotBlank(jyscal)){
			jyscal=jyscal.substring(0,jyscal.length()-1);
		}
		return jyscal;
	}
	
	
	public Page<CsshljCzForm> search(Page<CsshljCzForm> page,
			List<PropertyFilter> filters) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<CsshljCzForm> search(Page<CsshljCzForm> page, CsshljCzForm form) {

		Page pg = PageUtils.newPageInstance(page);
		
		StringBuffer hql = new StringBuffer("from Csshljcz ps where 1=1");
		List values = new ArrayList();
		
		if(form != null){
		    if (StringUtils.isNotBlank(form.getSqdw())) {
		        hql.append(" and ps.qymc like '%" + form.getSqdw() + "%'");
		    }
		    hql.append(" and ps.fzh is not NULL ");
		}
		
		hql.append(HqlUtils.buildOrderBy(page, "ps"));
		smartDao.findPage(pg, hql.toString(), values);
		List<CsshljCzForm> list = YwHwCzConvertor.convertVoListToFormList(pg.getResult());
		PageUtils.copy(pg, list, page);
		return page;
	}

	public void delete(Long... ids) {
		// TODO Auto-generated method stub
		
	}

	public CsshljCzForm get(Long id) {
		Csshljcz entity = null;
		Session session=csshljCzDao.getSession();
		Criteria criteria=session.createCriteria(Csshljcz.class);
		criteria.add(Restrictions.eq("id",id));
		List<Csshljcz> list=criteria.list();if(list!=null && list.size()>0){
		    entity=list.get(0);
		}
		CsshljCzForm czForm = null;
		czForm = YwHwCzConvertor.convertVoToForm(entity);
		return czForm;
	}
	
	//自动启动流程
	public void autoStartProc(CsshljCzForm form,String templateCode){
		if(form != null && form.getId() != null){
			//测试需要保存一下
		   //this.save(form);
			
			//设置汇总字段
			form.setSqdw(form.getQymc());
			form.setSarq(form.getSqrq());
			
			//流程变量处理，默认将主业务对象作为form变量
			Map<String, Object> params = new HashMap<String, Object>();
			params.put(WfConstant.VAR_MASTER_ENTITY_ALIAS, form);
			
			//表示是自动启动流程,用来控制流程在"经办人员调查"环节是否启动人员分配事件
//			String areaCode = "4";
			String userCode = wfServiceEX.findBoPlaceCode(null, 109l);//109是【废弃物管理中心经办人】角色ID 写死
			System.out.println("cccccccccc = "+userCode);
			params.put("isAutoStartProc", "1");
			params.put("var_jbr",userCode);
			//获取该区的流程启动者
			String stratUser = wfServiceEX.getUser("fwqzx_ck", null);//fwqzx_ck【废弃物中心窗口】
			WfBusInstanceTaskForm task = wfService.wfBusStart(templateCode, form, stratUser, params);
			String executionId =  processEngine.getHistoryService().createHistoryTaskQuery().taskId(task.getTaskInstDbid().toString()).uniqueResult().getExecutionId();
            processEngine.getExecutionService().jumpToActivity(executionId, "fqwzxjbr");//跳到【经办人现场勘察、初审】 环节     xyzxjbr是环节英文名称
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.csshlj.service.ICsshljCzService#updateData(java.util.Map)
	 */
	public void updateData(Map map) {
		String id=(String) map.get("id");
		String startdate=(String) map.get("starttime");
		String enddate=(String) map.get("endtime");
		String type=(String) map.get("type");
		String sql="";
		//后台构造日期格式
//		if(StringUtils.isNotBlank(startdate) &&startdate.length()>=10){
//			startdate=startdate.substring(0,4)+"年"+startdate.substring(5,7)+"月"+
//			startdate.substring(8,10)+"日";
//		}
//		if(StringUtils.isNotBlank(enddate) &&enddate.length()>=10){
//			enddate=enddate.substring(0,4)+"年"+enddate.substring(5,7)+"月"+
//			enddate.substring(8,10)+"日";
//		}
		if(StringUtils.isNotBlank(type)){
			if(type.indexOf("处置")>=0){
				sql=" update SHLJ_CZ_V1 set JYSJSSTR='"+startdate+"', ";
				sql+=" JYSJESTR='"+enddate+"' ";
				sql+=" where id="+id;
			}else if(type.indexOf("水域")>=0){
				sql=" update SYSHLJ_V1 set JYSJSSTR='"+startdate+"', ";
				sql+=" JYSJESTR='"+enddate+"' ";
				sql+=" where id="+id;
			}else if(type.indexOf("陆域")>=0){
				sql=" update HW_SHLJQS_V1 set JYSJSSTR='"+startdate+"', ";
				sql+=" JYSJESTR='"+enddate+"' ";
				sql+=" where id="+id;
			}
			this.csshljCzDao.updateDateBase(sql);
		}
		
	}
	
	//保存结案时间
	public void endSave(Long id) {		
		Csshljcz entity = csshljCzDao.get(id);
    	entity.setJarq(new Date());
    	csshljCzDao.save(entity);
	}
	
	/**
	 * 根据ID获得对象实体
	 * @param id
	 * @return
	 */  
	public Csshljcz getEntity(Long id){
		return csshljCzDao.get(id);
	}
	
	/**
     * 保存对象实体
     * @param csshljcz
     */     
    public void saveEntity(Csshljcz csshljcz){
    	csshljCzDao.save(csshljcz);
    }
    
    /**
     * 保存政务中心的统一查询编号和密码
     * @param id
     * @param serialNo
     * @param password
     */
    public void saveSerialNoAndPassword(Long id,String serialNo,String password) {
    	Csshljcz entity = csshljCzDao.get(id);
    	entity.setSerialNo(serialNo);
    	entity.setPassword(password);
    	csshljCzDao.save(entity);
    }
    
    /**
     * 根据受理号查出企业编号
     * @param acceptNo
     * @return
     */
    public String getQyidByAcceptNo(String acceptNo){
    	String sql = "select t.qybh from shlj_cz_v1 t where t.accept_no='"+acceptNo+"'";
    	SQLQuery query = smartDao.getSession().createSQLQuery(sql);
    	List list = query.list();
    	return (String)list.get(0);
    }

	@Override
	public void saveOherInfo(Map map) {
		String id=(String) map.get("id");
		String bjgzfcr=(String) map.get("bjgzfcr");
		String bjgzyy=(String) map.get("bjgzyy");
		String bjgzsj=(String) map.get("bjgzsj");
		String bjgzqd=(String) map.get("bjgzqd");
		String bjslsj=(String) map.get("bjslsj");
		String sldwcbrxm=(String) map.get("sldwcbrxm");
		String bjclqd=(String) map.get("bjclqd");
		String bjsljtdd=(String) map.get("bjsljtdd");
		String yqts=(String) map.get("yqts");
		if(StringUtils.isNotBlank(id)){
			Csshljcz entity = this.csshljCzDao.get(Long.valueOf(id));
			
			if(StringUtils.isNotBlank(bjgzsj)){
				Date bjgzdate;
				try {
					bjgzdate = DateUtilsEX.dateFormate(bjgzsj, "yyyy年MM月dd日");
					entity.setBjgzsj(bjgzdate);//补交告知时间
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			if(StringUtils.isNotBlank(bjslsj)){
				Date bjslsjdate;
				try {
					bjslsjdate = DateUtilsEX.dateFormate(bjslsj, "yyyy年MM月dd日");
					entity.setBjslsj(bjslsjdate);//补交受理时间
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			if(StringUtils.isNotBlank(bjgzfcr)){
				entity.setBjgzfcr(bjgzfcr);//补交告知发出人
			}
			if(StringUtils.isNotBlank(bjgzyy)){
				entity.setBjgzyy(bjgzyy);//补交告知发出人
			}
			if(StringUtils.isNotBlank(bjgzqd)){
				entity.setBjgzqd(bjgzqd);//补交告知发出人
			}
			if(StringUtils.isNotBlank(sldwcbrxm)){
				entity.setSldwcbrxm(sldwcbrxm);//补交告知发出人
			}
			if(StringUtils.isNotBlank(bjclqd)){
				entity.setBjclqd(bjclqd);//补交告知发出人
			}
			if(StringUtils.isNotBlank(bjsljtdd)){
				entity.setBjsljtdd(bjsljtdd);//补交告知发出人
			}
			if(StringUtils.isNotBlank(yqts)){
				entity.setYqts(yqts);//延期天数
			}
			
			this.csshljCzDao.save(entity);
		}
		
	}

	@Override
	public String getYqts(Long id) {
		String sql=" select yqts from SHLJ_CZ_V1 where id="+id;
		List list=this.csshljCzDao.queryForList(sql);
		if(list.size()>0){
			Map map=(Map) list.get(0);
			return (String) map.get("yqts");
		}
		
		
		return null;
	}
	public String wfBusYslTuih(CsshljCzForm form,LoginUserForm loginUser) throws Exception {
    	String  loginUserName = loginUser.getUser().getUserName();
    	WfBusInstanceTaskForm taskForm = new WfBusInstanceTaskForm();
		taskForm.setHandleComments("资料不全，不予受理。");
		//把审批状态数据保存到ywWebserviceInfo ，交换给方欣
		ywWebserviceInfoService.saveYwWebserviceInfo(form.getSqrq(), form.getAcceptNo(), form.getCbyw(), form.getJbrxm(), form.getSjr(), form.getSqdw(), 
				form.getBusinessCode(), form.getApprovalNo(), loginUserName, loginUser.getOrg().getOrgName(), "ysl", new Date(), false, "预受理", taskForm);
//		System.out.println("form.getYslThyy() = "+java.net.URLDecoder.decode(form.getYslThyy(),"UTF-8"));
		this.changeYslState(form.getId(), 0L,taskForm.getHandleComments());
		
		//（网办）交换到市民网页
		Map map=exchangeStateService.buildMap(loginUser, null, form.getId().toString(), form.getTemplateCode(), false);
		String durationExpression = wfServiceEX.getDurationExpressionByTemplateCode(form.getTemplateCode());
		ywWebserviceForWbService.setValueAndSaveYwWebserviceForWb(loginUserName,form.getTemplateCode(),
				form.getId().toString(),durationExpression,"001",taskForm.getHandleComments(), map);
    	return null;
    }

	@Override
	public void updateAcceptNo(Csshljcz form) {
		String hql = "update Csshljcz gd set gd.acceptNo='"+form.getAcceptNo()+"',gd.serialNo='"+form.getAcceptNo()+"' where gd.id=:id";
		Query query =this.csshljCzDao.getSession().createQuery(hql);
		query.setLong("id", form.getId());
		query.executeUpdate();
		
	}
}
